/*
	THIS PROCEDURE LOADS THE INVENTORY AVAILABLE FOR THE DATE SPECIFIED IN CHIPTRAC AND CALCULATES ITS COST.
select * from product
	EXEC sp_cost_reprort_by_product '0','0','0','0','0','0','0', 'xlreport'
	EXEC sp_cost_reprort_by_product '0','0','895','0','0','prjcostdetail','0', 'xlreport'
*/
Alter PROC sp_cost_reprort_by_product

@PROCESS_TYPE	VARCHAR(50),
@PRODUCTID	VARCHAR(50),
@PROCESSSTEP_ID	VARCHAR(200),
@VENDOR_ID	VARCHAR(50),
@ASOFDATE	VARCHAR(50),
@REPORT_TYPE	VARCHAR(20),
@DEFAULT_PROMPT	VARCHAR(50),
@REPORT_NAME    VARCHAR(20)
 
AS
BEGIN
	CREATE TABLE #COST_TABLE 
		( [LOT ID] INT, [Lot Number] VARCHAR(50), [PROCESSSTEP ID] INT, [LOT CATEGORY] VARCHAR(20),[WAFER QTY] INT, [QTY IN] INT,
		  [LOT COST] NUMERIC(18,4),[LOT CUMMULATIVE COST] NUMERIC(18,4) ,[PART NUMBER] VARCHAR(50),[BASE PRODUCT] VARCHAR(50),
		  [PROCESS] VARCHAR(50),[PROCESS TYPE NAME] VARCHAR(50), [PROCESS TYPE ID] INT,[VENDOR NAME] VARCHAR(50), [Projected Unit Cost] NUMERIC(18,4), [Total Projected Cost] NUMERIC(18,4) )

 	DECLARE @SQLSTRING NVARCHAR(4000)
	DECLARE @TRANSITSQLSTRING NVARCHAR(4000)

	DECLARE @TEMP_VARCHAR VARCHAR(50)
	DECLARE @START_DATE DATETIME
	DECLARE @END_DATE DATETIME

	DECLARE @LOT_ID INT
	DECLARE @PATH_PROCESSSTEP_ID INT
	DECLARE @QUARTER_NO	INT
	DECLARE @YEAR		INT
	DECLARE @PS_STEP_ID 	BIGINT
	DECLARE @PRJ_COST NUMERIC(10,4)
	DECLARE @PRJ_YIELD FLOAT
	DECLARE @SORT_STD_COST NUMERIC(10,4)
	DECLARE @SORT_YIELD FLOAT
	DECLARE @FAB_PROCESSSTEP_ID INT
	DECLARE @GDW INT
	DECLARE @TOTAL_STD_COST NUMERIC(10,4)
	DECLARE @NDW INT
	DECLARE @CUMM_STD_COST NUMERIC(10,4)
	DECLARE @PS_TYPE_NAME VARCHAR(20)
	DECLARE @PS_TYPE		VARCHAR(50)

	SELECT @YEAR = CC.CC_YEAR, @QUARTER_NO = CQC_QUARTER_NO FROM COMPANY_QUARTERLY_CALENDAR CQC, COMPANY_CALENDAR CC
		WHERE CQC.CQC_YEARID = CC.CC_YEARID 
		AND CURRENT_TIMESTAMP >= CQC_START_DATE 
		AND CURRENT_TIMESTAMP <= CQC_END_DATE 

	IF @ASOFDATE = '0'
	BEGIN
		SET @SQLSTRING = N'INSERT INTO #COST_TABLE
		SELECT 
			LTH.LOT_ID,LTH.Lot_Number, LTH.PROCESSSTEP_ID,LOT_CATEGORY, WAFER_QTY_IN, QTY_IN,
			LOT_COST, LOT_CUMMULATIVE_COST,PART_NUMBER,
			(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
				CASE 
					WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
					WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
				END 
			) AS BASE_PRODUCT, PS_TYPE, PS_TYPE_NAME,
			(SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = PS.PS_TYPE) AS PS_TYPE_ID,L_VENDOR_NAME, 0, 0
		FROM 
			LOT_TABLE LTH,PRODUCT P,PROCESS_STEP PS ,LOCATION L
		WHERE
			LTH.PROCESSSTEP_ID = P.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
			AND LTH.LOCATION_ID = L.LOCATION_ID
			AND P.P_TYPE IS NULL
			AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' ) AND LTH.Lot_Category = ''PROD'''
	END
	ELSE
	BEGIN
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 12:00AM'
		SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
		SET @TEMP_VARCHAR = NULL
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 11:59PM'
		SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)

		SET @SQLSTRING = N'INSERT INTO #COST_TABLE
		SELECT 
			LTH.LOT_ID,LTH.Lot_Number,LTH.PROCESSSTEP_ID,LTH.LOT_CATEGORY,LTH.WAFER_QTY_IN , LTH.QTY_IN,
			ISNULL(LTH.LOT_COST,LT.LOT_COST ), ISNULL(LTH.LOT_CUMMULATIVE_COST,LT.LOT_CUMMULATIVE_COST ) , PART_NUMBER,
			(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
				CASE 
					WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
					WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
				END 
			) AS BASE_PRODUCT, PS_TYPE,PS_TYPE_NAME,
			(SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = PS.PS_TYPE) AS PS_TYPE_ID, L_VENDOR_NAME, 0, 0 
		FROM 
			LOT_TABLE_HISTORY LTH, LOT_TABLE LT,PRODUCT P,PROCESS_STEP PS ,LOCATION L
		WHERE
			LTH.LOT_ID = LT.LOT_ID
			AND LTH.PROCESSSTEP_ID = P.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
			AND LTH.LOCATION_ID = L.LOCATION_ID
			AND P.P_TYPE IS NULL
			AND ( LTH.LOT_CURRENT_STATUS = ''RUNNING'' OR LTH.LOT_CURRENT_STATUS = ''HOLD'' ) AND LTH.Lot_Category = ''PROD''
			AND LTH.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''''
	END 		
	
	IF @PROCESS_TYPE IS NOT NULL AND @PROCESS_TYPE <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND PS_TYPE = '''+ @PROCESS_TYPE+''''

		IF @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND LTH.PROCESSSTEP_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PROCESSSTEP_ID +'''))'
		END
	END
	ELSE
	BEGIN
		IF @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND P_PARENT_PRODUCT_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PROCESSSTEP_ID +'''))'
		END
	END

	/*IF @PRODUCTID IS NOT NULL AND @PRODUCTID <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND P_PARENT_PRODUCT_ID = '''+ @PRODUCTID+''''
	END

	IF @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LTH.PROCESSSTEP_ID = '''+ @PROCESSSTEP_ID+''''
	END*/
	
	IF @VENDOR_ID IS NOT NULL AND @VENDOR_ID <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND L.VENDOR_ID = '''+ @VENDOR_ID+''''
	END

	--PRINT '@SQLSTRING = '+@SQLSTRING
	EXEC (@SQLSTRING)

	-- THIS IS FOR TRANSIT LOTS
	IF @ASOFDATE = '0'
	BEGIN
		SET @TRANSITSQLSTRING = N'INSERT INTO #COST_TABLE
		SELECT 
			LTH.LOT_ID,LTH.Lot_Number, LTH.PROCESSSTEP_ID,LOT_CATEGORY, CASE WHEN PS.PS_TYPE_NAME = ''FAB'' OR PS.PS_TYPE_NAME = ''WAFER STORE'' OR PS_TYPE_NAME = ''SORT'' THEN SHIP_Qty ELSE 0 END, 
			CASE WHEN PS.PS_TYPE_NAME <> ''FAB'' AND PS.PS_TYPE_NAME <> ''WAFER STORE'' AND PS_TYPE_NAME <> ''SORT'' THEN SHIP_Qty ELSE 0 END,
			SL.LOT_COST, SL.LOT_CUMMULATIVE_COST,PART_NUMBER,
			(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
				CASE 
					WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
					WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
				END 
			) AS BASE_PRODUCT, PS_TYPE, PS_TYPE_NAME,
			(SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = PS.PS_TYPE) AS PS_TYPE_ID,L_VENDOR_NAME, 0, 0
		FROM 
			LOT_TABLE LTH,PRODUCT P,PROCESS_STEP PS ,LOCATION L, Shipped_lot SL
		WHERE
			LTH.PROCESSSTEP_ID = P.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
			AND LTH.LOCATION_ID = L.LOCATION_ID
			AND SL.Lot_ID = LTH.Lot_ID
			AND P.P_TYPE IS NULL
			AND ( SL.STATUS = ''TRANSIT'' ) AND LTH.Lot_Category = ''PROD'''
	END
	ELSE
	BEGIN
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 12:00AM'
		SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
		SET @TEMP_VARCHAR = NULL
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 11:59PM'
		SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)

		SET @TRANSITSQLSTRING = N'INSERT INTO #COST_TABLE
		SELECT 
			LTH.LOT_ID,LTH.Lot_Number, LTH.PROCESSSTEP_ID,LOT_CATEGORY, CASE WHEN PS.PS_TYPE_NAME = ''FAB'' OR PS.PS_TYPE_NAME = ''WAFER STORE'' OR PS_TYPE_NAME = ''SORT'' THEN SHIP_Qty ELSE 0 END, 
			CASE WHEN PS.PS_TYPE_NAME <> ''FAB'' AND PS.PS_TYPE_NAME <> ''WAFER STORE'' AND PS_TYPE_NAME <> ''SORT'' THEN SHIP_Qty ELSE 0 END,
			SL.LOT_COST, SL.LOT_CUMMULATIVE_COST,PART_NUMBER,
			(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
				CASE 
					WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
					WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
				END 
			) AS BASE_PRODUCT, PS_TYPE, PS_TYPE_NAME,
			(SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = PS.PS_TYPE) AS PS_TYPE_ID,L_VENDOR_NAME, 0, 0
		FROM 
			LOT_TABLE LTH,PRODUCT P,PROCESS_STEP PS ,LOCATION L, Shipped_lot_History SL
		WHERE
			LTH.PROCESSSTEP_ID = P.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
			AND LTH.LOCATION_ID = L.LOCATION_ID
			AND SL.Lot_ID = LTH.Lot_ID
			AND P.P_TYPE IS NULL
			AND ( SL.STATUS = ''TRANSIT'' ) AND LTH.Lot_Category = ''PROD''
			AND SL.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''''
	END 		
	
	IF @PROCESS_TYPE IS NOT NULL AND @PROCESS_TYPE <> '0'
	BEGIN
		SET @TRANSITSQLSTRING = @TRANSITSQLSTRING + ' AND PS_TYPE = '''+ @PROCESS_TYPE+''''

		IF @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> '0'
		BEGIN
			SET @TRANSITSQLSTRING = @TRANSITSQLSTRING + ' AND LTH.PROCESSSTEP_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PROCESSSTEP_ID +'''))'
		END
	END
	ELSE
	BEGIN
		IF @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> '0'
		BEGIN
			SET @TRANSITSQLSTRING = @TRANSITSQLSTRING + ' AND P_PARENT_PRODUCT_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PROCESSSTEP_ID +'''))'
		END
	END

	IF @VENDOR_ID IS NOT NULL AND @VENDOR_ID <> '0'
	BEGIN
		SET @TRANSITSQLSTRING = @TRANSITSQLSTRING + ' AND L.VENDOR_ID = '''+ @VENDOR_ID+''''
	END

	EXEC (@TRANSITSQLSTRING)

	-- DELETE THE FAB WAFER COST FROM THE REPORT.
	DELETE FROM #COST_TABLE WHERE [PROCESSSTEP ID] IN ( SELECT ProcessStep_ID FROM PROCESS_STEP WHERE PS_TYPE = 'FAB' AND ProcessStep_ID NOT IN ( SELECT ProcessStep_ID FROM Process_Parameter WHERE Parameter_key = 'IS_MEMS_WAFER' AND Parameter_Value = 'TRUE' ) )
	
	IF @REPORT_NAME = 'Crystal'
	BEGIN
		SELECT  [PROCESSSTEP ID],[LOT CATEGORY],SUM([WAFER QTY]) [WAFER QTY],SUM([QTY IN]) [QTY IN],
			[LOT COST],SUM([LOT CUMMULATIVE COST]) [LOT CUMMULATIVE COST],[PART NUMBER],
			[BASE PRODUCT],[PROCESS],[PROCESS TYPE ID]--,[VENDOR NAME] 
		FROM #COST_TABLE
		GROUP BY [PROCESSSTEP ID],[LOT CATEGORY],[LOT COST], [PART NUMBER],[BASE PRODUCT],
			 [PROCESS],[PROCESS TYPE ID]--,[VENDOR NAME] 
	END
	ELSE IF @REPORT_NAME = 'XLREPORT'
	BEGIN
		IF @REPORT_TYPE = 'prjcostdetail'
		BEGIN
			CREATE TABLE #PROCESSSTEPS_CUMM_COST_TABLE ( LOT_ID INT, PROCESSSTEP_ID INT, CUMM_STD_COST NUMERIC(10,4), PS_TYPE_NAME VARCHAR(50) )

			CREATE TABLE #PATH_TABLE (ROW_ID INT, PATH_NUMBER INT ,PROCESSSTEP_ID INT, PS_TYPE VARCHAR(50), PRJ_COST NUMERIC(10,4), PRJ_YIELD FLOAT)

			INSERT INTO #PROCESSSTEPS_CUMM_COST_TABLE (LOT_ID, PROCESSSTEP_ID, PS_TYPE_NAME) SELECT DISTINCT [LOT ID],[PROCESSSTEP ID], [PROCESS TYPE NAME] FROM #COST_TABLE
			
			DECLARE @PROCESS_STEPS_CURSOR CURSOR
			DECLARE @PATH_CURSOR CURSOR 
			SET @PROCESS_STEPS_CURSOR = CURSOR FOR 
									SELECT DISTINCT PROCESSSTEP_ID FROM #PROCESSSTEPS_CUMM_COST_TABLE ORDER BY PROCESSSTEP_ID DESC
			
			OPEN @PROCESS_STEPS_CURSOR
			FETCH NEXT FROM @PROCESS_STEPS_CURSOR INTO @PS_STEP_ID
			WHILE @@FETCH_STATUS = 0
			BEGIN
				INSERT INTO #PATH_TABLE(ROW_ID, PATH_NUMBER, PROCESSSTEP_ID, PS_TYPE, PRJ_COST, PRJ_YIELD)
				SELECT ROW_ID, PATH_NUMBER, XYZ.PROCESSSTEP_ID, PS_TYPE, Projected_Cost, Projected_Yield
					FROM 
					(SELECT Distinct ROW_ID, PATH_NUMBER, PROCESSSTEP_ID, PS_TYPE 
						FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@PS_STEP_ID,0) ) AS XYZ 
					LEFT OUTER JOIN UPDATE_VIEW_COST_TABLE UVCT ON XYZ.PROCESSSTEP_ID = UVCT.PROCESSSTEP_ID AND YEAR(fin_year) = @YEAR
					 ORDER BY PATH_NUMBER, ROW_ID DESC
				
				SET @PATH_CURSOR = CURSOR FOR 
									SELECT PROCESSSTEP_ID, PS_TYPE, PRJ_COST, PRJ_YIELD FROM #PATH_TABLE WHERE PATH_NUMBER = 1 ORDER BY ROW_ID DESC
				OPEN @PATH_CURSOR
				FETCH NEXT FROM @PATH_CURSOR INTO @PATH_PROCESSSTEP_ID, @PS_TYPE, @PRJ_COST, @PRJ_YIELD
				WHILE @@FETCH_STATUS = 0
				BEGIN
										
					IF ( @PS_TYPE = 'DIE BANK' )
					BEGIN
						SELECT @SORT_STD_COST = MAX(ISNULL(PRJ_COST,0)) FROM #PATH_TABLE WHERE PS_TYPE = 'SORT'
						SELECT @SORT_YIELD = MAX(ISNULL(PRJ_YIELD,0)) FROM #PATH_TABLE WHERE PS_TYPE = 'SORT' AND ISNULL(PRJ_COST,0) = ISNULL(@SORT_STD_COST,0)
						SELECT @FAB_PROCESSSTEP_ID = PROCESSSTEP_ID FROM #PATH_TABLE WHERE PS_TYPE = 'FAB'
						SELECT @GDW = PARAMETER_VALUE FROM Process_Parameter WHERE PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID AND PARAMETER_KEY = 'Gross Die/Wafer'
						IF ( @SORT_YIELD IS NULL OR @SORT_YIELD = 0 )
						BEGIN
							SET @SORT_YIELD = 100
						END 
						SET @NDW = @GDW * (@SORT_YIELD/100)
											
						IF ( @NDW > 0 )
						BEGIN
							SET @TOTAL_STD_COST = ISNULL(@TOTAL_STD_COST,0)/@NDW
						END
					END
					
					IF ( @PATH_PROCESSSTEP_ID = @PS_STEP_ID ) --NOT CONSIDERING YEIELD AS THE CURRENT PROCESS IS NOT COMPLETED
					BEGIN
						SET @TOTAL_STD_COST = ISNULL(@TOTAL_STD_COST,0) + ISNULL(@PRJ_COST,0)
					END
					ELSE
					BEGIN
						IF ( @PRJ_YIELD IS NULL OR @PRJ_YIELD = 0 )
						BEGIN
							SET @PRJ_YIELD = 100.00
						END
						
						SET @TOTAL_STD_COST = ((100*ISNULL(@TOTAL_STD_COST,0) )/@PRJ_YIELD) + (ISNULL(@PRJ_COST,0))
					END

					SET @SORT_STD_COST = 0
					SET @SORT_YIELD = 0
					SET @FAB_PROCESSSTEP_ID = NULL
					SET @GDW = 0
					SET @NDW = 0
					FETCH NEXT FROM @PATH_CURSOR INTO @PATH_PROCESSSTEP_ID, @PS_TYPE, @PRJ_COST, @PRJ_YIELD
				END
				CLOSE @PATH_CURSOR
				DEALLOCATE @PATH_CURSOR

				DELETE FROM #PATH_TABLE
				
				UPDATE #PROCESSSTEPS_CUMM_COST_TABLE SET CUMM_STD_COST = @TOTAL_STD_COST WHERE PROCESSSTEP_ID = @PS_STEP_ID
				
				SET @TOTAL_STD_COST = 0
				FETCH NEXT FROM @PROCESS_STEPS_CURSOR INTO @PS_STEP_ID
			END
			CLOSE @PROCESS_STEPS_CURSOR
			DEALLOCATE @PROCESS_STEPS_CURSOR
			
			DECLARE @LOT_CURSOR CURSOR
			SET @LOT_CURSOR = CURSOR FOR SELECT LOT_ID, CUMM_STD_COST, PS_TYPE_NAME FROM #PROCESSSTEPS_CUMM_COST_TABLE
			OPEN @LOT_CURSOR
			FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID, @CUMM_STD_COST, @PS_TYPE_NAME
			WHILE @@FETCH_STATUS = 0
			BEGIN
				IF ( @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT')
				BEGIN
					UPDATE #COST_TABLE SET [Projected Unit Cost] = @CUMM_STD_COST, [Total Projected Cost] = ( [WAFER QTY] * ISNULL( @CUMM_STD_COST, 0.0 ) ) WHERE [LOT ID] = @LOT_ID
				END
				ELSE
				BEGIN
					UPDATE #COST_TABLE SET [Projected Unit Cost] = @CUMM_STD_COST, [Total Projected Cost] = ( [QTY IN] * ISNULL( @CUMM_STD_COST, 0.0 ) ) WHERE [LOT ID] = @LOT_ID
				END
				SET @LOT_ID = NULL
				FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID, @CUMM_STD_COST, @PS_TYPE_NAME
			END
			CLOSE @LOT_CURSOR
			DEALLOCATE @LOT_CURSOR 

			SELECT  [BASE PRODUCT],[PART NUMBER],[PROCESS],SUM([WAFER QTY]) AS [WAFER QTY],SUM([QTY IN]) AS [ DIE QTY],
				   [LOT COST] AS [Std Unit Cost],[Projected Unit Cost],([LOT COST]- [Projected Unit Cost] ) as [Unit Cost Delta], SUM([LOT CUMMULATIVE COST]) AS [Total Cost],  
					SUM([Total Projected Cost] ) AS [Total Projected Cost], ( SUM([LOT CUMMULATIVE COST])-SUM([Total Projected Cost] )  ) AS [Total Cost Delta]
			FROM #COST_TABLE 
			GROUP BY [BASE PRODUCT],[PROCESS TYPE ID],[PART NUMBER],[PROCESS],[LOT COST],[Projected Unit Cost]
			
		END
		ELSE
		BEGIN
			SELECT  [BASE PRODUCT],[PART NUMBER],[PROCESS],[VENDOR NAME],[Lot Number],([WAFER QTY]) AS [WAFER QTY],([QTY IN]) AS [ DIE QTY],
				   [LOT COST] AS [Unit Cost],([LOT CUMMULATIVE COST]) AS [TOTAL COST]
			FROM #COST_TABLE 
			--GROUP BY [BASE PRODUCT],[PROCESS TYPE ID],[PART NUMBER],[PROCESS],[LOT COST]
		END
	END
	

END
